/**
 * 项目名：phms3
 * 日期：    2019-11-21 18:27:53
 * Copyright (c) 2015- HTSC-版权所有
 */

package com.joysuch.wwyt.mh.repository;

import com.joysuch.wwyt.mh.entity.MhAlarmMessage;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.query.Param;

import javax.persistence.Tuple;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * MhAlarmMessageDao
 */
public interface MhAlarmMessageDao
        extends JpaRepository<MhAlarmMessage, Long>, QuerydslPredicateExecutor<MhAlarmMessage> {

    public Page<MhAlarmMessage> findAll(Specification<MhAlarmMessage> spec, Pageable pageable);

    public MhAlarmMessage save(MhAlarmMessage bean);

    public void delete(MhAlarmMessage bean);

    @Query(value = "select * from wwyt_mh_alarm_message where monitoring_point_id = ?1 and delete_flag = '0' order by create_time desc limit 1", nativeQuery = true)
    MhAlarmMessage selectLastAlarmMessage(Long pointId);

    @Query(value = "SELECT m1.* FROM wwyt_mh_alarm_message m1 INNER JOIN ( SELECT MAX( id ) AS id FROM wwyt_mh_alarm_message GROUP BY monitoring_point_id ) m2 ON m1.id = m2.id WHERE m1.monitoring_point_id IN (?1)", nativeQuery = true)
    List<MhAlarmMessage> selectLastAlarmMessages(List<Long> pointIds);

    @Query(value = "select  * from wwyt_mh_alarm_message where alarm_time > ?1 and delete_flag = '0' order by alarm_time asc", nativeQuery = true)
    List<MhAlarmMessage> statsForCountInDay(LocalDateTime time);

    @Query(value = "select  * from wwyt_mh_alarm_message where monitoring_point_id = ?1 and delete_flag = '0' order by alarm_time asc limit ?2", nativeQuery = true)
    List<MhAlarmMessage> selectLastAlarmMessagesOfMinitoringPoint(Long monitoringPointId, int limit);

    @Query(value = "    select '重大危险源' as module,id,create_time,tenent_id,org_code,state from wwyt_mh_alarm_message    union "
            + "    select '风险分区' as module,id,create_time,tenent_id,org_code,state from wwyt_risk_evaluation_alarm_mesage e "
            + " where tenent_id = ?1 and org_code like ?2% "
            + "    order by create_time desc limit ?3,?4 ;", nativeQuery = true)
    List<Tuple> selectAlarmMessageUnion(Long tenentId, String orgCode, int offset, int size);

    @Query(value = "select  * from wwyt_mh_alarm_message where delete_flag = '0'order by create_time desc limit 0,100", nativeQuery = true)
    List<MhAlarmMessage> findTop100OrderByCreateTimeDesc();

    @Query(value = "select  * from wwyt_mh_alarm_message where state=?1 and delete_flag = '0' order by create_time desc limit 0,100", nativeQuery = true)
    List<MhAlarmMessage> findTop100ByStateOrderByCreateTimeDesc(Integer state);

    @Query(value = "select  * from wwyt_mh_alarm_message where state between 0 and 1 and delete_flag = '0' order by create_time desc limit 0,100", nativeQuery = true)
    List<MhAlarmMessage> findTop100ByStateOrderByCreateTimeDesc();

    @Query(value = "select count(*) from wwyt_mh_alarm_message where state between 0 and 1 and delete_flag = '0'", nativeQuery = true)
    int countByStateIn();

    @Query(value = "select count(1) from wwyt_mh_alarm_message where state in (0,1) and delete_flag = '0' limit 1 ", nativeQuery = true)
    int checkCount(Long tenentId, String OrgCode);

    @Query(value = "select count(*) from wwyt_mh_alarm_message where state between 0 and 1 and delete_flag = '0' and  tenent_id =?1 and  org_code =?2", nativeQuery = true)
    int countByStates(Long tenentId, String OrgCode);

    public List<MhAlarmMessage> findByCreateTimeAfter(LocalDateTime time);

    int countByCreateTimeAfter(LocalDateTime time);

    @Query(value = "update wwyt_mh_alarm_message set delete_flag='1' where monitoring_point_id = ?1 and state =2 and delete_flag = '0'", nativeQuery = true)
    @Modifying
    void removeHandledMessagesByMonitoringPointId(Long monitoringPointId);

    // @Query(value = "select m from MhAlarmMessage m WHERE state=:state AND tententId =:tenentId AND orgCode =:orgCode
    // and if(:startDate != null, alarmTime>= :startDate, alarmTime <=:endDate) ")
    // List<MhAlarmMessage> queryMhAlarmMessage(@Param("tenentId") Long tenentId, @Param("orgCode") String orgCode,
    // @Param("state") Integer state,
    // @Param("startDate") Date startDate, @Param("endDate") Date endDate);

    List<MhAlarmMessage> findAllByAlarmTimeBetween(LocalDateTime startTime, LocalDateTime endTime);


    @Query(value = "select  * from wwyt_mh_alarm_message where monitoring_point_id = ?1 and delete_flag = '0' AND state!=2 order by alarm_time asc", nativeQuery = true)
    List<MhAlarmMessage> selectAlarmMessagesOfMinitoringPoint(Long monitoringPointId);

    @Query(value = "select  * from wwyt_mh_alarm_message where monitoring_point_id = ?1 and delete_flag = '0' AND state!=2  " +
            "and ALARM_TIME BETWEEN ?2 AND ?3 " +
            "order by alarm_time asc ", nativeQuery = true)
    List<MhAlarmMessage> selectAlarmMessagesOfMinitoringPointAndTime(Long id, Date startTime, Date endTime);

    @Query(value = "select  * from wwyt_mh_alarm_message where monitoring_point_id = ?1 and delete_flag = '0'", nativeQuery = true)
    List<MhAlarmMessage> findByMhPointId(Long monitoringPointId);

    @Query(value = "SELECT COUNT(1) FROM wwyt_mh_alarm_message WHERE MONITORING_POINT_ID IN" +
            "(SELECT p.MONITORING_POINT_ID FROM wwyt_bp_device_facility_monitoring_point AS p WHERE p.DEVICE_ID IN" +
            "(SELECT id FROM wwyt_bp_device_facility_collection WHERE major_hazard_id = ?1 AND DELETE_FLAG ='0') AND p.DELETE_FLAG ='0')" +
            " AND state = ?2 AND DELETE_FLAG ='0'", nativeQuery = true)
    int countMhNumber(Long id, int i);

    @Query(value = "SELECT COUNT(1) FROM wwyt_mh_alarm_message WHERE MONITORING_POINT_ID =?1 AND state !=?2 AND DELETE_FLAG ='0'", nativeQuery = true)
    int countByMhPointIdAndStateNotEq(Long id, int i);

    @Query(value = "select distinct monitoring_point_id from wwyt_mh_alarm_message where state!=?1 and delete_flag = '0'", nativeQuery = true)
    List<Long> findPointIdsByState(Integer state);

    @Query(value = "select  MONITORING_POINT_ID from wwyt_mh_alarm_message where id =?1 and delete_flag = '0'", nativeQuery = true)
    Long getPointId(Long id);

    @Query(value = "select * from wwyt_mh_alarm_message where monitoring_point_id in (?1) and delete_flag = '0' and state!=2 order by id desc limit 1", nativeQuery = true)
    MhAlarmMessage getLastAlarmMessageByPointIds(List<Long> ids);

    @Query(value = "select distinct (MONITORING_POINT_ID) from wwyt_mh_alarm_message where id in (?1) and delete_flag = '0'", nativeQuery = true)
    List<Long> getPointIdsByIds(List<Long> alarmsIds);

    @Modifying
    @Query(value = "UPDATE wwyt_mh_alarm_message SET STATE = 2 ,REMARK = ?1 ,ALARM_REASON =?2 ,HANDLE_BY =?3 ,HANDLE_TIME=?4 WHERE DELETE_FLAG = '0' AND  STATE !=2", nativeQuery = true)
    void setAllMeaageHandled(String msg, String alarmReason, String handleBy, LocalDateTime now);

    @Query(value = "select * from wwyt_mh_alarm_message where monitoring_point_id =?1 and delete_flag = '0' and state!=2", nativeQuery = true)
    List<MhAlarmMessage> findByMhPointIdAndStateNotEq(Long pointId);

    @Query(value = "select * from wwyt_mh_alarm_message where delete_flag = '0' and state!=2 and level_name IS NOT NULL", nativeQuery = true)
    List<MhAlarmMessage> findByMhPointByState();

    @Modifying
    @Query(value = "UPDATE wwyt_mh_alarm_message SET level_name = ?2,push_time=?3 WHERE id = ?1", nativeQuery = true)
    void setlevelNameById(Long id, String levelName, LocalDateTime now);

    @Query(value = "SELECT\n" +
            "\tCOUNT( * ) AS num,\n" +
            "\tDATE_FORMAT( CREATE_TIME, '%Y-%m-%d' ) AS `day` \n" +
            "FROM\n" +
            "\t`wwyt_mh_alarm_message` \n" +
            "WHERE\n" +
            "\tDATE_FORMAT( CREATE_TIME, '%Y-%m-%d' ) BETWEEN ?1 AND ?2\n" +
            "GROUP BY\n" +
            "\tDATE_FORMAT( CREATE_TIME, '%Y-%m-%d' )", nativeQuery = true)
    List<Map<String, String>> alarmTrendStatistics(String startTime, String endTime);

}
